Locate matching cells in multiple worksheets & report on value associated with match

[manleyjd14]

I have a spreadsheet that supplies server names and associated applications, but doesn't supply storage related data. That data is in another spreadsheet in multiple worksheets. For simplicity sake I created one spreadsheet with multiple worksheets. The Server & Application List worksheet contains server name I want to match in the other two worksheets. It's also where I'd like the storage data to be retrieved and stored.

I want to match servers in Server & Application list worksheet (column E) with server names in column A in the Physical Server list and Virtual Server list worksheets. Once matched I want to pull the STG data from column d and insert it into the appropriate line in column F in the Server & Application list worksheet.

I'm looking for the formula to read both the Physical Server and Virtual Server list worksheets.

Appreciate and help you can provide.

[FDibbins]

both VLOOKUP and INDEX/MATCH work across multiple files, and can be applied in the same manner as you would across different tabs. See if you can work with that, if not, shout back and we can help you further

[manleyjd14]

Ford... I'm struggling to understand if I need to do vlookup alone or in combo with index/match. All the vlookups I've coded have been simply find a match and play in a specific place. In this case I want to find the server name match, then pull the storage amount associated with that match and place it in a specific cell. I rarely have to do things like this so anything you could do to guide me in the right direction would be appreciated. Thanks

[Gregb11]

Try this in F3, then copy down:

=IFERROR(INDEX('Physical Server STG'!$D$2:$D$4,MATCH(E3,'Physical Server STG'!$A$2:$A$4,0)),INDEX('Virtual Server STG'!$D$2:$D$4,MATCH(E3,'Virtual Server STG'!$A$2:$A$4,0)))

[manleyjd14]

That did the trick. Thanks so much for your help!

[Gregb11]

You're welcome. Glad it worked.