match single cell across multiple sheets?

Hello,

I have a range H7:H27 in a summary tab, and for each item in that range I would like excel to search across sheets A, B, C, etc. and return a specific value based on an identical match with the item. The value to be returned in the winning sheet from A, B, C is not positioned alongside the matching cell in that sheet.

For example, for H7 in the summary, can Excel find a match from F8 in one of the other 3 sheets, and then based on that match return a value in H23 on the matched sheet?

I have been playing around with index/match and indirect, but am new to these formulas...

Thank you for your help!!

Why would you return data from H23? Does this assume that you matched with data in F23 on that sheet, or is the return range offset in some way from the search range?

Pete

Hi Pete,
Thanks for your reply. It's unfortunately not possible to match using F23 on that sheet. The return and search ranges are really only single cells - and they are positioned such that they can't be aligned, or repeated. I know this isn't the best excel practice in terms of layout, but perhaps there is still a solution?

It's difficult to visualise exactly how your data is laid out, so it would help if you attached a sample Excel workbook. To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

In the meantime, the way you would approach this would be along the lines of (in, say, I7 of the summary sheet):

=IFERROR("get from A if it is in A", IFERROR("get from B if it is in B", IFERROR("get from C if it is in C", "")))

where "get from A if it is in A" would be something like:

INDEX('A'!$H$23,MATCH(H7,'A'!$F$8,0))

and similar for the other sheets. I'm not sure what you want for cell I8 - would you be looking in cell F9 of the other sheets and returning from H24 if there is a match? If so, you would omit the $ from H$23 and F$8 and just copy the formula down.

Hope this helps.

Pete